﻿using NPOI.HSSF.UserModel;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

internal class ReadExcel
{
    public static List<List<string>> ReadExcelFile(string filePath, int index)
    {
        var result = new List<List<string>>();
        try
        {
            using (var file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
            {
                IWorkbook workbook;
                // 根据文件扩展名创建不同的工作簿
                if (Path.GetExtension(filePath).ToLower() == ".xlsx"){workbook = new XSSFWorkbook(file);}
                else{workbook = new HSSFWorkbook(file);}
                // 获取第一个工作表
                ISheet sheet = workbook.GetSheetAt(index);
                // 获取数据范围
                int rowCount = sheet.LastRowNum;
                // 遍历每一行
                for (int row = 0; row <= rowCount; row++)
                {
                    IRow currentRow = sheet.GetRow(row);
                    if (currentRow == null) continue;
                    var rowData = new List<string>();
                    int cellCount = currentRow.LastCellNum;
                    // 遍历每一列
                    for (int col = 0; col < cellCount; col++)
                    {
                        ICell cell = currentRow.GetCell(col);
                        string cellValue = GetCellValue(cell);
                        rowData.Add(cellValue);
                    }
                    result.Add(rowData);
                }
            }
            return result;
        }
        catch (Exception )
        {
            return result;
        }
    }

    // 获取单元格值
    private static string GetCellValue(ICell cell)
    {
        if (cell == null) return string.Empty;

        switch (cell.CellType)
        {
            case CellType.Numeric:
                // 处理日期类型
                if (DateUtil.IsCellDateFormatted(cell))
                {
                    return cell.DateCellValue.ToString();
                }
                else
                {
                    return cell.NumericCellValue.ToString();
                }
            case CellType.String:
                return cell.StringCellValue;
            case CellType.Boolean:
                return cell.BooleanCellValue.ToString();
            case CellType.Formula:
                return cell.CellFormula;
            default:
                return string.Empty;
        }
    }

    public static List<List<string>> ReadExcelFile(string filepath, string tabName)
    {
        var result = new List<List<string>>();
        using (FileStream fs = new FileStream(filepath, FileMode.Open, FileAccess.Read))
        {
            IWorkbook workbook = new XSSFWorkbook(fs);
            var isheet = workbook.GetSheet(tabName);
            int rowNum = isheet.LastRowNum;
            for (int i = 0; i <= rowNum; i++)
            {
                IRow currentRow = isheet.GetRow(i);
                if (currentRow == null) continue;
                var rowData = new List<string>();
                int cellCount = currentRow.LastCellNum;
                for (int col = 0; col < cellCount; col++)
                {
                    ICell cell = currentRow.GetCell(col);
                    string cellValue = GetCellValue(cell);
                    rowData.Add(cellValue);
                }
                result.Add(rowData);
            }
            return result;
        }
    }

    public static List<List<string>> ReadExcelFile2(string filePath, int index)
    {
        var result = new List<List<string>>();
        IFormulaEvaluator evaluator = null; // 新增：公式计算器

        try
        {
            using (var file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
            {
                IWorkbook workbook;
                // 根据文件扩展名创建不同的工作簿
                if (Path.GetExtension(filePath).ToLower() == ".xlsx")
                {
                    workbook = new XSSFWorkbook(file);
                    evaluator = new XSSFFormulaEvaluator(workbook as XSSFWorkbook); // 初始化xlsx计算器
                }
                else
                {
                    workbook = new HSSFWorkbook(file);
                    evaluator = new HSSFFormulaEvaluator(workbook as HSSFWorkbook); // 初始化xls计算器
                }

                // 获取指定工作表
                ISheet sheet = workbook.GetSheetAt(index);
                if (sheet == null) return result;

                // 获取数据范围
                int rowCount = sheet.LastRowNum;

                // 遍历每一行
                for (int row = 0; row <= rowCount; row++)
                {
                    IRow currentRow = sheet.GetRow(row);
                    if (currentRow == null) continue;

                    var rowData = new List<string>();
                    int cellCount = currentRow.LastCellNum;

                    // 遍历每一列
                    for (int col = 0; col < cellCount; col++)
                    {
                        ICell cell = currentRow.GetCell(col, MissingCellPolicy.CREATE_NULL_AS_BLANK);
                        // 关键修改：传入计算器获取计算后的值
                        string cellValue = GetCellValue(cell, evaluator);
                        rowData.Add(cellValue);
                    }

                    result.Add(rowData);
                }
            }
            return result;
        }
        catch (Exception ex)
        {
            // 建议添加错误日志
            Console.WriteLine($"读取Excel错误: {ex.Message}");
            return result;
        }
    }

    // 修改：增加evaluator参数用于计算公式
    private static string GetCellValue(ICell cell, IFormulaEvaluator evaluator)
    {
        if (cell == null)
            return string.Empty;

        // 关键修改：如果是公式单元格，先计算公式
        if (cell.CellType == CellType.Formula && evaluator != null)
        {
            cell = evaluator.EvaluateInCell(cell); // 用计算结果替换公式
        }

        // 处理不同类型的单元格值
        switch (cell.CellType)
        {
            case CellType.String:
                return cell.StringCellValue;

            case CellType.Numeric:
                // 处理日期类型
                if (DateUtil.IsCellDateFormatted(cell))
                    return cell.DateCellValue.ToString();
                // 处理数字类型，避免科学计数法
                return cell.NumericCellValue.ToString("G");

            case CellType.Boolean:
                return cell.BooleanCellValue.ToString();

            case CellType.Error:
                return $"#ERROR_{cell.ErrorCellValue}";

            default:
                return string.Empty;
        }
    }
}